Connecting to MySQL Using Command-Line Tools

Course: MySQL Training

Topic: Connecting to MySQL Using Command-Line Tools

Official Course: JustAcademy MySQL Training Course

Course Demo: Register for MySQL Course Demo

1. Introduction

Connecting to MySQL using command-line tools means accessing a MySQL database server through a terminal or command prompt by entering commands. Instead of using a graphical interface, users can connect to the server, execute SQL statements, retrieve records, create databases, manage tables, and perform administrative tasks through text-based commands.

The MySQL command-line client, commonly known as mysql, is one of the most widely used tools for interacting with MySQL Server. It supports interactive sessions, where users enter SQL statements one at a time, as well as non-interactive execution, where SQL statements are read from files or supplied through scripts.

Command-line access is useful for developers, database administrators, students, application support teams, and anyone who needs direct access to a MySQL database.

2. Learning Objectives

• Understand command-line database connectivity.
• Learn about the MySQL command-line client.
• Understand the difference between MySQL Server and the MySQL client.
• Connect to a local MySQL server.
• Connect to a remote MySQL server.
• Understand connection options such as username, password, host, and port.
• Execute SQL statements through the terminal.
• Select databases and inspect tables.
• Run SQL scripts from files.
• Identify common connection errors and troubleshoot them.
• Follow secure database connection practices.

3. What Is a Command-Line Tool?

A command-line tool is a program controlled by entering text commands into a terminal or command prompt. It allows users to perform operations without relying on graphical menus, buttons, or windows.

For MySQL, command-line tools allow users to connect to the database server and run SQL queries directly.

Examples of Command-Line Environments

4. What Is the MySQL Command-Line Client?

The MySQL command-line client is a client program that connects to a running MySQL Server. After a successful connection, users can enter SQL statements and receive results directly in the terminal.

The executable is generally named mysql. It is included with many MySQL installations or client packages, although its availability depends on the installed software.

Common Uses

• Connecting to a MySQL database server.
• Creating and managing databases.
• Creating tables and defining columns.
• Inserting, updating, deleting, and retrieving records.
• Checking database and server information.
• Executing SQL files.
• Testing connectivity during troubleshooting.
• Performing database maintenance tasks with appropriate privileges.

5. MySQL Server vs. MySQL Client

6. Prerequisites for Connecting to MySQL

Before connecting through the command line, make sure the following requirements are met:

1. MySQL client: The mysql command-line client must be installed or available in your system's executable path.
2. MySQL Server: A MySQL Server must be running locally or on a reachable remote machine.
3. Username: You need a valid MySQL account.
4. Password: If the account requires a password, you need the correct credentials.
5. Network access: For remote connections, the client must be able to reach the server through the required network route and port.
6. Permissions: The account must have permission to connect and perform the intended database operations.

Check Whether the Client Is Installed

Open your terminal or command prompt and execute:

mysql --version

If the client is installed and available in the system path, the command displays its version information.

If the command is not recognized, the client may be missing or its installation directory may not be included in the system's PATH environment variable.

7. Basic MySQL Connection Syntax

The basic command for connecting to MySQL is:

mysql -u username -p

Here, -u specifies the MySQL username and -p tells the client to prompt for the password.

Example

mysql -u root -p

After entering this command, MySQL displays a password prompt. Enter the password for the account. The password usually does not appear on the screen while you type it.

Important: Use the root account only when administrative access is needed. For regular application or learning tasks, a dedicated account with limited privileges is preferable.

8. Connecting to a Local MySQL Server

A local connection is made when the MySQL client connects to a server running on the same computer.

Step 1: Open the Terminal

Open Command Prompt or PowerShell on Windows, Terminal on macOS, or a terminal application on Linux.

Step 2: Check the MySQL Server

On many Linux installations, you can check the service using:

sudo systemctl status mysql

Some installations use the service name mysqld instead. Windows and macOS may use different service-management methods depending on how MySQL was installed.

Step 3: Connect to MySQL

mysql -u root -p

Step 4: Enter Your Password

Enter the password when prompted. If authentication succeeds, the client opens a MySQL session.

Step 5: Verify the Connection

SELECT VERSION();
SELECT CURRENT_USER();
SHOW DATABASES;

The first statement displays the server version, the second shows the authenticated MySQL account, and the third lists databases visible to the connected account.

9. Connecting to MySQL on a Specific Host

When the MySQL server is running on another computer or a particular network address, use the host option.

Syntax

mysql -h hostname -u username -p

Example: Connect to a Localhost TCP Address

mysql -h 127.0.0.1 -u root -p

This explicitly specifies the IPv4 loopback address. Depending on the operating system and client options, connecting to localhost may use a local socket or another local transport rather than TCP.

Example: Connect to a Remote Server

mysql -h 192.168.1.100 -u appuser -p

Replace 192.168.1.100 with the actual server hostname or IP address. The example works only if the server is reachable, accepts connections on the selected interface, and allows the account to connect from the client's host.

10. Connecting Through a Specific Port

MySQL commonly uses TCP port 3306. If the server uses a different port, specify it with the -P option. The uppercase letter is important because -p is used for password prompting.

Syntax

mysql -h hostname -P port_number -u username -p

Example

mysql -h 127.0.0.1 -P 3307 -u root -p

This command attempts to connect to the server at 127.0.0.1 using TCP port 3307.

11. Important MySQL Connection Options

12. Connecting Directly to a Database

You can specify the database name when starting the client. This saves you from having to select the database manually after connecting.

Syntax

mysql -u username -p database_name

Example

mysql -u appuser -p company_db

After authentication, MySQL selects company_db as the current database if the account has access to it.

You can also use the -D option:

mysql -u appuser -p -D company_db

13. Understanding the MySQL Command Prompt

After connecting successfully, the client typically displays a prompt similar to:

mysql>

This prompt indicates that the client is ready to accept SQL statements or supported client commands.

Example

mysql> SELECT VERSION();
mysql> SHOW DATABASES;
mysql> USE company_db;
mysql> SHOW TABLES;

SQL statements are normally terminated with a semicolon (;). The client sends the statement to the server and displays the returned result or error.

14. Executing Basic SQL Commands

Once connected, you can execute SQL statements to work with databases and tables.

Display Available Databases

SHOW DATABASES;

Create a Database

CREATE DATABASE training_db;

Select a Database

USE training_db;

Create a Table

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100) NOT NULL,
    email VARCHAR(150),
    course VARCHAR(100)
);

Insert a Record

INSERT INTO students (student_name, email, course)
VALUES ('Amit Sharma', '[email protected]', 'MySQL');

Retrieve Records

SELECT * FROM students;

Update a Record

UPDATE students
SET course = 'Advanced MySQL'
WHERE student_id = 1;

Delete a Record

DELETE FROM students
WHERE student_id = 1;

Warning: Always verify the WHERE condition before executing an UPDATE or DELETE statement. Without an appropriate condition, the operation may affect every row in the table.

15. Useful MySQL Client Commands

The MySQL command-line client supports special client commands in addition to SQL statements.

Client commands are interpreted by the mysql program, whereas SQL statements are sent to MySQL Server. Some client commands are available only in interactive mode or can be disabled through client options.

16. Checking the Connection Status

After connecting, you can inspect the session to confirm which server and account you are using.

Display Connection Status

status

Display the Current User

SELECT USER();
SELECT CURRENT_USER();

USER() reports the user identity supplied by the client and its connection origin, while CURRENT_USER() identifies the MySQL account used for privilege checking.

Display the Current Database

SELECT DATABASE();

If no database has been selected, this query returns NULL.

17. Running SQL Files from the Command Line

SQL files contain one or more SQL statements saved in a text file, usually with a .sql extension. The MySQL client can execute these statements without requiring you to type each one manually.

Example SQL File

Create a file named setup.sql containing:

CREATE DATABASE IF NOT EXISTS training_db;
USE training_db;
CREATE TABLE IF NOT EXISTS courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL
);
INSERT INTO courses (course_name)
VALUES ('MySQL Fundamentals');

Execute the SQL File

mysql -u root -p < setup.sql

On shells that support input redirection, the file contents are sent to the MySQL client. Enter the password when prompted.

Execute a File Against a Specific Database

mysql -u appuser -p training_db < setup.sql

Use an account with the privileges required by every statement in the file. Review scripts before executing them, especially if they contain commands that change or delete data.

Save Output to a File

mysql -u appuser -p training_db < report.sql > report_output.txt

This redirects the client's standard output to a text file. Password prompts and errors may be handled through separate output streams depending on the shell and environment.

18. Connecting on Windows

On Windows, MySQL can be accessed through Command Prompt or PowerShell if the MySQL client is installed.

Steps

1. Open Command Prompt or PowerShell.
2. Check whether the client is available by running mysql --version.
3. If the command is not recognized, locate the MySQL installation's bin directory or configure the appropriate PATH entry.
4. Run the connection command.

mysql -u root -p

If the executable is not in PATH, use its full installation path. For example, a typical installation may have a path resembling:

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" -u root -p

The exact folder name depends on the installed version and installation location.

19. Connecting on macOS

On macOS, use the Terminal application to run the MySQL client.

Check the Client

mysql --version

Connect to MySQL

mysql -u root -p

If the command is not found, verify that the MySQL client is installed and that its executable directory is included in PATH. The steps differ depending on whether MySQL was installed using the official installer, Homebrew, or another package.

20. Connecting on Linux

On Linux, the MySQL client can be started from a terminal. The server may be installed on the same system or hosted remotely.

Check the Client Version

mysql --version

Connect to the Local Server

mysql -u root -p

Check the Server Service

sudo systemctl status mysql

If your distribution uses the service name mysqld, use that service name instead. On some systems, administrative accounts may be configured to authenticate through operating-system credentials or a socket-based authentication mechanism. Follow the authentication method configured for your installation.

21. Understanding Local and Remote Connections

22. Common Connection Errors and Troubleshooting

Connection problems can occur because of incorrect credentials, a stopped server, network restrictions, or client configuration issues. Read the complete error message before deciding how to fix the problem.

Useful Troubleshooting Checklist

1. Verify that the MySQL client is installed.
2. Confirm the correct server hostname or IP address.
3. Check the port number.
4. Confirm that MySQL Server is running.
5. Verify the account credentials and authentication method.
6. Check whether the account is permitted to connect from the client's host.
7. For remote connections, review firewall rules and server listening addresses.
8. Read the server error log if the issue appears to originate on the server.
9. Check TLS requirements if encrypted connections are mandatory.

23. Security Best Practices for Command-Line Connections

• Use password prompting: Prefer mysql -u username -p instead of placing the password directly in the command.
• Avoid exposed passwords: Passwords typed directly in commands may be stored in shell history or visible through process information.
• Use least privilege: Connect with an account that has only the permissions needed for the task.
• Protect remote access: Restrict database network access to trusted systems.
• Use TLS where appropriate: Configure encrypted connections when data travels across untrusted networks or when required by policy.
• Protect saved credentials: If using supported option files or login-path tools, restrict access to their files and follow the relevant security guidance.
• Review scripts: Inspect SQL files before executing them against important databases.
• Be careful with destructive commands: Verify targets before running DROP, DELETE, or broad UPDATE statements.
• Disconnect when finished: Exit sessions that are no longer needed.

24. Practical Example: Connect and Manage a Student Database

Objective: Connect to MySQL using the command-line client, create a database and table, insert sample data, and retrieve the results.

Step 1: Connect to MySQL

mysql -u root -p

Step 2: Create a Database

CREATE DATABASE student_training;

Step 3: Select the Database

USE student_training;

Step 4: Create a Table

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150),
    course VARCHAR(100)
);

Step 5: Insert Sample Records

INSERT INTO students (name, email, course)
VALUES
('Rahul', '[email protected]', 'MySQL Basics'),
('Priya', '[email protected]', 'Database Fundamentals'),
('Neha', '[email protected]', 'SQL Programming');

Step 6: Display the Records

SELECT * FROM students;

Step 7: Check the Selected Database

SELECT DATABASE();

Step 8: Exit the MySQL Client

exit

Expected Learning Outcome

After completing this exercise, you should be able to establish a connection, select a database, create a table, insert records, run a query, and exit the client safely.

25. Advantages of Using MySQL Command-Line Tools

• Fast execution: Frequently used database tasks can be performed directly through commands.
• Automation: SQL scripts can be integrated into scheduled jobs and deployment workflows.
• Remote administration: Users can connect to authorized database servers without requiring a graphical interface.
• Low overhead: A command-line client can be useful on servers without a desktop environment.
• Flexible querying: SQL statements can be entered and executed directly.
• Useful diagnostics: Connection options and status commands help test database access.
• Script integration: Input and output redirection can support repeatable database tasks.

26. Command-Line Tools vs. Graphical Database Tools

Both approaches are useful. The choice depends on the task, environment, user experience, and operational requirements.

27. Frequently Asked Questions

Q1. What is the MySQL command-line client?

It is a program named mysql that connects to MySQL Server and allows users to execute SQL statements through a terminal.

Q2. How do I connect to MySQL from the terminal?

Run mysql -u username -p and enter the account password when prompted.

Q3. What does the -u option mean?

It specifies the MySQL account name used for authentication.

Q4. What is the difference between -p and -P?

Lowercase -p is used for password prompting. Uppercase -P specifies the TCP port number.

Q5. How can I connect to a remote MySQL server?

Use the host option, for example mysql -h db.example.com -u appuser -p, with the actual hostname, correct credentials, and appropriate network access.

Q6. How do I select a database after connecting?

Run USE database_name;.

Q7. How do I see all databases?

Execute SHOW DATABASES;. The results depend on the privileges of your account.

Q8. How do I exit the MySQL client?

Type exit, quit, or \q.

Q9. Can I execute SQL files from the terminal?

Yes. You can use input redirection, such as mysql -u username -p database_name < script.sql, or use the client's source command during an interactive session.

Q10. Why is MySQL returning an access-denied error?

The credentials may be incorrect, the account may not be permitted to connect from the client's host, or the authentication method may not match the setup.

Q11. Is the MySQL server required to be installed on the same computer?

No. The client can connect to a local server or a remote server when the connection is permitted and correctly configured.

Q12. Is it safe to include a password in a command?

It is generally safer to use -p without specifying the password value so the client prompts for it. Avoid exposing credentials in shell history, scripts, or process information.

28. Practice Questions

1. What is a command-line tool?
2. Explain the role of the MySQL command-line client.
3. Differentiate between MySQL Server and the MySQL client.
4. Write the command to connect to MySQL using a username and password prompt.
5. How do you connect to a server using a specific hostname and port?
6. What is the difference between localhost and 127.0.0.1 for MySQL connections?
7. How do you select a database after connecting?
8. Which command displays the available databases?
9. Explain how to execute an SQL file from the command line.
10. What are the common causes of a connection-refused error?
11. Why should passwords not be placed directly in command-line arguments?
12. Describe the steps for troubleshooting an access-denied error.

29. Conclusion

Connecting to MySQL using command-line tools is a fundamental database skill. The mysql client provides a direct way to connect to a server, execute SQL statements, inspect databases, manage tables, run SQL scripts, and diagnose connection issues.

By understanding connection options, host and port settings, authentication, client commands, and security best practices, developers and database administrators can work with MySQL efficiently in local, remote, and automated environments.

Learn More: JustAcademy MySQL Training Course

Register for a Demo: Register for MySQL Course Demo